package com.hp.bon.sgw.util;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.LineNumberReader;
import java.util.ArrayList;
import java.util.Map;
import java.util.Properties;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.Transaction;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;

/**
 * 初始化数据库,添加必要的数据，每次系统启动时会调用init()方法完成数据库的初始化工作，特别适合于配合测试.
 * 对于小数据量的初始化，建议采用编程的方式实现，这样可以省却为不同数据库写特定sql脚本的事情
 * 对于大批数据的初始化，可以考虑采用sql脚本来实现（为不同数据库写不同脚本，命名为ORACAL_XX.sql,HSQL_XX,sql等）
 * 
 * @author spring
 */
public class DBInitiator {
	/**
	 * Logger for this class.
	 */
	private static final Logger logger = LoggerFactory.getLogger(DBInitiator.class);

	/** Hibernate SessionFactory. */
	private SessionFactory sessionFactory;

	/**
	 * 加载的SQL 文件.
	 */
	private String[] sqlFiles;

	private boolean enabled;

	/**
	 * 该方法完成数据准备的工作.
	 */
	public void init() {
		// 从数据库加载初始化SQL脚本
		if (enabled) {
			initDBfromSqlFiles();
		}

	}

	private void initDBfromSqlFiles() {
		for (String sqlFile : sqlFiles) {
			logger.info("read sql file " + sqlFile);
			// InputStream inStream = getClass().getClassLoader()
			// .getResourceAsStream(sqlFile);
			InputStream inStream = null;
			try {
				inStream = new FileInputStream(sqlFile);
				logger.info("execute sql file " + sqlFile);
				initDBfromSqlFile(inStream);
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				if (inStream != null) {
					try {
						inStream.close();
					} catch (IOException e) {
						e.printStackTrace();
					}
				}
			}

		}

	}

	/**
	 * 加载数据库脚本初始化数据库.
	 * 
	 * @param inStream
	 *            inStream
	 * @return String 执行结果
	 */
	public String initDBfromSqlFile(InputStream inStream) {
		String result = null;
		String[] sqls = readSQLFile(inStream);

		Session session = sessionFactory.openSession();
		Transaction trans = session.beginTransaction();
		int curSqlIndex = 0;

		String curSql = null;
		try {
			int updateCount = 0;
			if (sqls != null) {
				for (int i = 0; i < sqls.length; i++) {
					curSqlIndex = i;
					curSql = sqls[i];
					updateCount += session.createSQLQuery(curSql).executeUpdate();
				}
			}
			logger.info("  affected rows  " + updateCount);
			trans.commit();
			result = "success!<br>" + "  affected rows  " + updateCount;
		} catch (Exception err) {
			logger.error(" error occured  cur sql " + curSqlIndex + " " + curSql, err);
			result = "error!<br>" + " error occured  cur sql: <br>" + curSql;
			trans.rollback();
		} finally {
			session.close();
		}
		// loadPropertiesToDB();
		return result;
	}

	protected void loadPropertiesToDB() {
		String name = "sgwchannel-cached.properties";
		Properties proies = Comutils.getPropertiesFromFile(name);

		Session session = sessionFactory.openSession();
		Transaction trans = session.beginTransaction();
		int curSqlIndex = 0;
		String curSql = null;

		try {
			int updateCount = 0;
			if (proies != null) {
				for (Map.Entry<Object, Object> entry : proies.entrySet()) {
					String key = (String) entry.getKey();
					String value = (String) entry.getValue();
					curSql = "insert into  CONFIGURATION (key,value) values(?,?)";
					SQLQuery query = session.createSQLQuery(curSql);
					query.setParameter(0, key);
					query.setParameter(1, value);
					updateCount += query.executeUpdate();
				}
			}
			logger.info("  affected rows  " + updateCount);
			trans.commit();

		} catch (Exception err) {
			logger.error(" error occured  cur sql " + curSqlIndex + " " + curSql, err);

			trans.rollback();
		} finally {
			session.close();
		}

	}

	/**
	 * 将SQL资源文件读到String[].
	 * 
	 * @param resourceName
	 *            sql resouce
	 * @return String[]
	 */
	private String[] readSQLFile(InputStream inStream) {
		ArrayList<String> sqlAry = new ArrayList<String>();
		if (inStream == null) {
			logger.warn("sql resouce file not exits ");
			return new String[0];
		}
		LineNumberReader lineReader = null;
		try {
			lineReader = new LineNumberReader(new InputStreamReader(inStream, "UTF-8"));
			String sql = lineReader.readLine();
			while (sql != null) {
				sql = sql.trim();
				if (!"".equals(sql) && sql.charAt(0) != '#') {
					sqlAry.add(sql);

				}
				sql = lineReader.readLine();
			}
			return sqlAry.toArray(new String[sqlAry.size()]);
		} catch (IOException e) {
			logger.error("read sql file err", e);
			return new String[0];
		} finally {

			// 关闭Stream
			if (lineReader != null) {
				try {
					lineReader.close();
				} catch (IOException e) {
					logger.warn("close file err ", e);
				}
			}

		}
	}

	/**
	 * @param pSessionFactory
	 *            the pSessionFactory to set
	 */
	@Autowired
	@Qualifier("sessionFactory")
	public void setSessionFactory(SessionFactory pSessionFactory) {
		this.sessionFactory = pSessionFactory;
	}

	/**
	 * @param pSqlFiles
	 *            the sqlFiles to set
	 */
	public void setSqlFiles(String[] pSqlFiles) {
		this.sqlFiles = pSqlFiles;
	}

	public void setEnabled(boolean enabled) {
		this.enabled = enabled;
	}

}
